if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ThanhVien_GetALLThanhVien]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ThanhVien_GetALLThanhVien]
GO

CREATE PROCEDURE [sp_ThanhVien_GetALLThanhVien]
@MaChiNhanh int,
@MaThanhVien int
AS
if @MaChiNhanh = 0
	Begin
		SELECT	A.id as MaThanhVien , 
				A.Ten_dang_nhap as TenDangNhap ,
				A.Mat_khau as MatKhau,
				C.Ten as TenVaiTro ,
				a.ma_vai_tro as MaVaiTro,
				a.ma_chi_nhanh as MaChiNhanh,
				D.Ten as TenChiNhanh , 
				B.Ho_ten as HoTen ,
				B.Ngay_sinh as NgaySinh , 
				B.Dia_chi as DiaChi ,
				B.Email ,
				B.Tel ,
				B.Bang_cap as BangCap ,
				CASE B.Tinh_trang WHEN '1' THEN 'Unblock' ELSE 'Block' END AS TinhTrang
		from thanhvien a 
		inner join chitietthanhvien b on a.id = b.ma_thanh_vien 
		inner join vaitro c on a.Ma_vai_tro = c.ID
		inner join chinhanh d on a.Ma_chi_nhanh = d.Id
		where a.id != @MaThanhVien
	END
ELSE
	Begin
		SELECT	A.id as MaThanhVien , 
				A.Ten_dang_nhap as TenDangNhap ,
				A.Mat_khau as MatKhau,
				C.Ten as TenVaiTro ,
				a.ma_vai_tro as MaVaiTro,
				a.ma_chi_nhanh as MaChiNhanh,
				D.Ten as TenChiNhanh , 
				B.Ho_ten as HoTen ,
				B.Ngay_sinh as NgaySinh , 
				B.Dia_chi as DiaChi ,
				B.Email ,
				B.Tel ,
				B.Bang_cap as BangCap ,
				CASE B.Tinh_trang WHEN '1' THEN 'Unblock' ELSE 'Block' END AS TinhTrang
		from thanhvien a 
		inner join chitietthanhvien b on a.id = b.ma_thanh_vien 
		inner join vaitro c on a.Ma_vai_tro = c.ID
		inner join chinhanh d on a.Ma_chi_nhanh = d.Id
		where a.ma_chi_nhanh = @MaChiNhanh and a.id != @MaThanhVien
	End